package com.gxa.dao.impl;

import com.alibaba.fastjson.annotation.JSONField;
import com.alibaba.fastjson.serializer.SerializerFeature;
import com.gxa.dao.HouseDao;
import com.gxa.dto.HouseDto;
import com.gxa.entity.Build;
import com.gxa.entity.Community;
import com.gxa.entity.House;
import com.gxa.entity.Owner;
import com.gxa.entity.extend.QueryAllHouse;
import com.gxa.utils.DBConnection;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class HouseDaoImpl implements HouseDao {
    @Override
    public List<House> queryHouse() throws SQLException {
        List<House> houseList = new ArrayList<>();
        Connection connection = DBConnection.getConnection();
        String sql = "SELECT h.hid,\n" +
                "       h.propertyid,\n" +
                "       h.house_num,\n" +
                "       h.count,\n" +
                "       h.live_time,\n" +
                "       c.cid,\n" +
                "       c.cname,\n" +
                "       b.bid,\n" +
                "       b.bname,\n" +
                "       b.unit,\n" +
                "       o.oid,\n" +
                "       o.oname,\n" +
                "       o.tel\n" +
                "FROM owner o,\n" +
                "     house h,\n" +
                "     community c,\n" +
                "     build b\n" +
                "WHERE h.community_id = c.cid\n" +
                "  AND h.build_id = b.bid\n" +
                "  AND h.owner_id = o.oid\n";

        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = connection.prepareStatement(sql);
            rs = ps.executeQuery();

            while (rs.next()) {
                int hid = rs.getInt("hid");
                String propertyid = rs.getString("propertyid");
                int house_num = rs.getInt("house_num");
                int count = rs.getInt("count");
                Date live_time = rs.getDate("live_time");

                // 小区
                int cid = rs.getInt("cid");
                String cname = rs.getString("cname");

                // 栋数
                int bid = rs.getInt("bid");
                String bname = rs.getString("bname");
                int unit = rs.getInt("unit");

                // 业主
                int oid = rs.getInt("oid");
                String oname = rs.getString("oname");
                String tel = rs.getString("tel");


                Community community = new Community(cid, cname); // 小区

                Build build = new Build(bid, bname, unit); // 栋数

                Owner owner = new Owner(oid, oname, tel); // 业主

                House house = new House(hid, propertyid, house_num, count, live_time, community, build, owner);
                houseList.add(house);

            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBConnection.close(rs, ps, connection);
        }

        return houseList;
    }


    @Override
    public List<House> queryAllHouse() throws SQLException {
        List<House> houseList = new ArrayList<>();
        Connection connection = DBConnection.getConnection();
        String sql = "SELECT h.hid,c.*,b.*,h.propertyid,o.*,o.tel,h.count,b.unit,h.house_num,h.live_time\n" +
                "FROM house h\n" +
                "         JOIN community c\n" +
                "              ON h.community_id = c.cid\n" +
                "         JOIN build b\n" +
                "              ON h.build_id = b.bid\n" +
                "         JOIN `owner` o\n" +
                "              ON h.owner_id = o.oid\n" +
                "ORDER BY h.hid ASC ";

        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = connection.prepareStatement(sql);
            rs = ps.executeQuery();

            while (rs.next()) {
                int hid = rs.getInt("hid");
                // 小区
                int cid = rs.getInt("cid");
                String cname = rs.getString("cname");
                String address = rs.getString("address");
                double area = rs.getDouble("area");
                String creator = rs.getString("creator");
                String managers = rs.getString("managers");
                Date create_time = rs.getDate("create_time");
                String remark = rs.getString("remark");

                // 楼栋
                int bid = rs.getInt("bid");
                String bname = rs.getString("bname");
                int unit_num = rs.getInt("unit_num");
                int unit = rs.getInt("unit"); // 单元
                int community_id = rs.getInt("community_id");


                String propertyid = rs.getString("propertyid");

                // 业主
                int oid = rs.getInt("oid");
                String oname = rs.getString("oname");
                String tel = rs.getString("tel");
                String card = rs.getString("card");
                String sex = rs.getString("sex");
                Date birthday = rs.getDate("birthday");
                String job = rs.getString("job");
                int type = rs.getInt("type");

                int count = rs.getInt("count");
                int house_num = rs.getInt("house_num");
                Date live_time = rs.getDate("live_time");

                Community community = new Community(cid, cname, address, area, creator, managers, create_time, remark); // 小区


                Build build = new Build(bid, bname, unit, unit_num, community);
                Owner owner = new Owner(oid, oname, tel, card, sex, birthday, job, type);
                House house = new House(hid, propertyid, house_num, count, live_time, community, build, owner);
                houseList.add(house);

            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBConnection.close(rs, ps, connection);
        }

        return houseList;

    }

    @Override
    public List<House> selectAllHouseByPage(int page, int limit) throws SQLException {
        List<House> houseList = new ArrayList<>();
        Connection connection = DBConnection.getConnection();
        String sql = "SELECT h.hid,\n" +
                "       h.propertyid,\n" +
                "       h.house_num,\n" +
                "       h.count,\n" +
                "       h.live_time,\n" +
                "       c.cid,\n" +
                "       c.cname,\n" +
                "       b.bid,\n" +
                "       b.bname,\n" +
                "       b.unit,\n" +
                "       o.oid,\n" +
                "       o.oname,\n" +
                "       o.tel\n" +
                "FROM owner o,\n" +
                "     house h,\n" +
                "     community c,\n" +
                "     build b\n" +
                "WHERE h.community_id = c.cid\n" +
                "  AND h.build_id = b.bid\n" +
                "  AND h.owner_id = o.oid\n" +
                "LIMIT ?,?";
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            ps = connection.prepareStatement(sql);
            ps.setInt(1, (page - 1) * limit);
            ps.setInt(2, limit);

            rs = ps.executeQuery();

            while (rs.next()) {
                int hid = rs.getInt("hid");
                String propertyid = rs.getString("propertyid");
                int house_num = rs.getInt("house_num");
                int count = rs.getInt("count");
                Date live_time = rs.getDate("live_time");

                // 小区
                int cid = rs.getInt("cid");
                String cname = rs.getString("cname");

                // 栋数
                int bid = rs.getInt("bid");
                String bname = rs.getString("bname");
                int unit = rs.getInt("unit");

                // 业主
                int oid = rs.getInt("oid");
                String oname = rs.getString("oname");
                String tel = rs.getString("tel");


                Community community = new Community(cid, cname); // 小区

                Build build = new Build(bid, bname, unit); // 栋数

                Owner owner = new Owner(oid, oname, tel); // 业主

                House house = new House(hid, propertyid, house_num, count, live_time, community, build, owner);
                houseList.add(house);

            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBConnection.close(rs, ps, connection);
        }

        return houseList;
    }

    @Override
    public List<House> selectHouseByHouseDto(Integer page, Integer limit, HouseDto houseDto) throws SQLException {
        List<House> houseList = new ArrayList<>();
        Connection connection = DBConnection.getConnection();
        StringBuilder sql = new StringBuilder(
                "SELECT h.hid,\n" +
                        "       h.propertyid,\n" +
                        "       h.house_num,\n" +
                        "       h.count,\n" +
                        "       h.live_time,\n" +
                        "       c.cid,\n" +
                        "       c.cname,\n" +
                        "       b.bid,\n" +
                        "       b.bname,\n" +
                        "       b.unit,\n" +
                        "       o.oid,\n" +
                        "       o.oname,\n" +
                        "       o.tel\n" +
                        "FROM owner o,\n" +
                        "     house h,\n" +
                        "     community c,\n" +
                        "     build b\n" +
                        "WHERE h.community_id = c.cid\n" +
                        "  AND h.build_id = b.bid\n" +
                        "  AND h.owner_id = o.oid\n ");

        if (houseDto != null) {
            if (houseDto.getHid() != null) {
                sql.append(" and h.hid=?");
            }
            if (houseDto.getCname() != null && !"".equals(houseDto.getCname())) {
                sql.append(" and c.cname=?");
            }
            if (houseDto.getOname() != null && !"".equals(houseDto.getOname())) {
                sql.append(" and o.oname=?");
            }
        }
        sql.append(" ORDER BY h.hid ASC");
        sql.append(" limit ?,?");

        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = connection.prepareStatement(sql.toString());
            int index = 1;

            if (houseDto != null) {
                if (houseDto.getHid() != null) {
                    ps.setInt(index, houseDto.getHid());
                    index++;
                }
                if (houseDto.getCname() != null && !"".equals(houseDto.getCname())) {
                    ps.setString(index, houseDto.getCname());
                    index++;
                }
                if (houseDto.getOname() != null && !"".equals(houseDto.getOname())) {
                    ps.setString(index, houseDto.getOname());
                    index++;
                }
            }
            ps.setInt(index, (page - 1) * limit);
            index++;
            ps.setInt(index, limit);
            index++;

            rs = ps.executeQuery();

            while (rs.next()) {
                int hid = rs.getInt("hid");
                String propertyid = rs.getString("propertyid");
                int house_num = rs.getInt("house_num");
                int count = rs.getInt("count");
                Date live_time = rs.getDate("live_time");

                // 小区
                int cid = rs.getInt("cid");
                String cname = rs.getString("cname");

                // 栋数
                int bid = rs.getInt("bid");
                String bname = rs.getString("bname");
                int unit = rs.getInt("unit");

                // 业主
                int oid = rs.getInt("oid");
                String oname = rs.getString("oname");
                String tel = rs.getString("tel");


                Community community = new Community(cid, cname); // 小区

                Build build = new Build(bid, bname, unit); // 栋数

                Owner owner = new Owner(oid, oname, tel); // 业主

                House house = new House(hid, propertyid, house_num, count, live_time, community, build, owner);
                houseList.add(house);

            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBConnection.close(rs, ps, connection);
        }

        return houseList;
    }

    @Override
    public Integer count() throws SQLException {
        Connection connection = DBConnection.getConnection();
        String sql = "SELECT COUNT(h.hid) as total " +
                "FROM owner o,house h,community c,build b " +
                "WHERE h.community_id = c.cid AND h.build_id = b.bid AND h.owner_id = o.oid ";

        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = connection.prepareStatement(sql);
            rs = ps.executeQuery();

            if (rs.next()) {
                int total = rs.getInt("total");
                return total;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBConnection.close(rs, ps, connection);
        }

        return 0;
    }

    @Override
    public Integer count(HouseDto houseDto) throws SQLException {
        Connection connection = DBConnection.getConnection();
        StringBuilder sql = new StringBuilder(
                "SELECT COUNT(h.hid) as total " +
                        "FROM owner o,house h,community c,build b " +
                        "WHERE h.community_id = c.cid AND h.build_id = b.bid AND h.owner_id = o.oid ");

        if (houseDto != null) {
            if (houseDto.getHid() != null) {
                sql.append(" and h.hid=?");
            }
            if (houseDto.getCname() != null && !"".equals(houseDto.getCname())) {
                sql.append(" and c.cname=?");
            }
            if (houseDto.getOname() != null && !"".equals(houseDto.getOname())) {
                sql.append(" and o.oname=?");
            }
        }

        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            ps = connection.prepareStatement(sql.toString());
            int index = 1;

            if (houseDto != null) {
                if (houseDto.getHid() != null) {
                    ps.setInt(index, houseDto.getHid());
                    index++;
                }
                if (houseDto.getCname() != null && !"".equals(houseDto.getCname())) {
                    ps.setString(index, houseDto.getCname());
                    index++;
                }
                if (houseDto.getOname() != null && !"".equals(houseDto.getOname())) {
                    ps.setString(index, houseDto.getOname());
                    index++;
                }
            }

            rs = ps.executeQuery();
            if (rs.next()) {
                int total = rs.getInt("total");
                return total;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBConnection.close(rs, ps, connection);
        }


        return 0;
    }

    @Override
    public int insertHouse(House house) throws SQLException {
        // 1、注册驱动、获取连接
        Connection connection = DBConnection.getConnection();
        PreparedStatement ps = null;
        // 2、准备SQL
        String sql = "INSERT INTO house (propertyid, house_num, count, live_time, community_id, build_id, owner_id) VALUES (?,?,?,?,?,?,?)";
        int result = 0;
        try {
            // 3、获取预编译对象，预编译SQL
            ps = connection.prepareStatement(sql);
            // 4、传入参数
            ps.setString(1, house.getPropertyId());
            ps.setInt(2, house.getHouseNum());
            ps.setInt(3, house.getCount());

            long time = house.getLiveTime().getTime(); // 获取时间戳来转换，因为不管是java.util.Date还是java.sql.Date都与时间戳有关
            Date liveTime = new Date(time); // java.sql.Date
            ps.setDate(4, liveTime);

            ps.setInt(5, house.getCommunity().getCid());
            ps.setInt(6, house.getBuild().getBid());
            ps.setInt(7, house.getOwner().getOid());
            // 5、执行SQL语句
            result = ps.executeUpdate();
            // 6、若为查询操作，需要处理查询结果集

        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            // 7、释放资源
            DBConnection.close(null, ps, connection);
        }

        return result;
    }

    @Override
    public int updateHouse(House house) throws SQLException {
        // 1、注册驱动、获取连接
        Connection connection = DBConnection.getConnection();
        PreparedStatement ps = null;
        // 2、准备SQL
        String sql = "UPDATE house\n" +
                "SET propertyid   = ?,\n" +
                "    house_num    = ?,\n" +
                "    count        =?,\n" +
                "    live_time    = ?,\n" +
                "    community_id = ?,\n" +
                "    build_id     = ?,\n" +
                "    owner_id     = ?\n" +
                "WHERE hid = ?";
       /* if (house != null) { // 带有条件
            if (house.getPropertyId() != null && !"".equals(house.getPropertyId())) {
                sql.append(" AND propertyid = ? ");
            }
            if (house.getHouseNum() != null) {
                sql.append(" house_num  = ?");
            }
            if (house.getCount() != null) {
                sql.append(" count = ?");
            }
            if (house.getLiveTime() != null) {
                sql.append(" live_time = ?");
            }
            if (house.getCommunity().getCid() != null) {
                sql.append(" community_id = ?");
            }
            if (house.getBuild().getBid() != null) {
                sql.append(" build_id = ?");
            }
            if (house.getOwner().getOid() != null) {
                sql.append(" owner_id  = ?");
            }
        }*/

        int result = 0;
        try {
            // 3、获取预编译对象，预编译SQL
            ps = connection.prepareStatement(sql);
            // 4、传入参数
            ps.setString(1, house.getPropertyId());
            ps.setInt(2, house.getHouseNum());
            ps.setInt(3, house.getCount());

            long time = house.getLiveTime().getTime(); // 获取时间戳来转换，因为不管是java.util.Date还是java.sql.Date都与时间戳有关
            Date liveTime = new Date(time); // java.sql.Date
            ps.setDate(4, liveTime);

            ps.setInt(5, house.getCommunity().getCid());
            ps.setInt(6, house.getBuild().getBid());
            ps.setInt(7, house.getOwner().getOid());
            ps.setInt(8, house.getHid());
            // 5、执行SQL语句
            result = ps.executeUpdate();
            // 6、若为查询操作，需要处理查询结果集

        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            // 7、释放资源
            DBConnection.close(null, ps, connection);
        }

        return result;
    }

    @Override
    public int delHouseById(Integer hidToInteger) throws SQLException {
        // 1、注册驱动、获取连接
        Connection connection = DBConnection.getConnection();
        PreparedStatement ps = null;
        // 2、准备SQL
        String sql = "DELETE FROM house WHERE hid = ?";
        int result = 0;
        try {
            // 3、获取预编译对象，预编译SQL
            ps = connection.prepareStatement(sql);
            // 4、传入参数
            ps.setInt(1,hidToInteger);

            // 5、执行SQL语句
            result = ps.executeUpdate();
            // 6、若为查询操作，需要处理查询结果集

        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            // 7、释放资源
            DBConnection.close(null, ps, connection);
        }

        return result;

    }
}
